/*
 * Copyright (c) 2008 Allurent, Inc.
 * http://www.allurent.com/
 *
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the
 * "Software"), to deal in the Software without restriction, including
 * without limitation the rights to use, copy, modify, merge, publish,
 * distribute, sublicense, and/or sell copies of the Software, and to
 * permit persons to whom the Software is furnished to do so, subject to
 * the following conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
 * LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
 * OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
 * WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
package com.allurent.database
{
    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.events.Event;
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;
    import flash.utils.Dictionary;
    
    import mx.logging.ILogger;
    import mx.logging.Log;
    
    /**
     * Manages a pool of prepared statements. This class
     * works in combination with the ConnectionPool to
     * request the SQLConnection that should be used
     * to perform the requested query.
     */  
    public class StatementPool
    {
        /**
         * Represents returning an unlimited number of rows for a query.
         */
        public static const UNLIMITED:int = -1;

        private static var _myLogger:ILogger = Log.getLogger("com.allurent.database.StatementPool");
        
        // Where connections should come from.
        private var _connectionPool:ConnectionPool;
        // Association between a connection and its prepared statements.
        private var _queries:Dictionary = new Dictionary();
        
        /**
         * Create a new statement pool using the supplied connection pool.
         * @param connectionPool Connection pool to use.
         */ 
        public function StatementPool(connectionPool:ConnectionPool)
        {
            _connectionPool = connectionPool;    
        }
        
        /**
         * Reset the statement pool. This discards any cached
         * prepared statements. 
         */
        public function reset():void
        {
            _queries = new Dictionary();    
        }
        
        /**
         * Execute a query, applying the given parameters, and returning the result to the handler.
         * The parameters Object represents an associative array of key/value pairs that correspond
         * to identifiers (minus the ":" prefix) present in the query. The query limit represents
         * the maximum number of rows that the query can return. This utility class does not
         * supply facilities to break up large record set loading through the use of
         * SQLStatement's next() method.
         * @param query Query to execute.
         * @param parameters Parameters to apply to query, or null if none need to be applied.
         * @param handler Function to get SQLResult object.
         * @param queryLimit The maximum number of rows to return for the query.
         */ 
        public function execute(query:String, parameters:Object, handler:Function, queryLimit:int = UNLIMITED):void
        {
            // See priority enhancement notes in ConnectionPool.getConnection().
            // If update queries are being used see the notes in
            // ConnectionPool.openConnection().
            _connectionPool.getConnection(function(sqlConnection:SQLConnection):void
            {
                processStatement(sqlConnection, query, parameters, handler, queryLimit);    
            });
        }
        
        /**
         * Once a connection has been obtained this will prepare the statement or grab the
         * already prepared statement that is associated with the SQL connection.
         * @param sqlConnection SQL Connection to use to run the statement.
         * @param query Query to execute.
         * @param parameters Parameters to apply to query.
         * @param handler Function to get SQLResult object.
         * @param queryLimit The maximum number of rows to return for the query.
         */ 
        private function processStatement(sqlConnection:SQLConnection, query:String, parameters:Object, handler:Function, queryLimit:int):void
        {
            _myLogger.debug("process statement");
            var preparedQueries:Object = _queries[sqlConnection];
            if (preparedQueries == null)
            {
                preparedQueries = new Object();
                _queries[sqlConnection] = preparedQueries;
            }
            var sqlStatement:SQLStatement = SQLStatement(preparedQueries[query]);
            if (sqlStatement == null)
            {
                _myLogger.debug("Creating new statement");
                sqlStatement = new SQLStatement();
                preparedQueries[query] = sqlStatement;
                sqlStatement.addEventListener(SQLErrorEvent.ERROR, function(sqlErrorEvent:SQLErrorEvent):void
                {
                    // Basic error handling that just does error reporting
                    // with no attempt at error recovery.
                    _myLogger.error("Statement error {0}", sqlErrorEvent);
                    // When an error happens, remove this bad statement from the cache.
                    delete preparedQueries[query];
                    // Also return the connection to the pool.
                    _connectionPool.returnConnection(sqlStatement.sqlConnection);
                });
                sqlStatement.sqlConnection = sqlConnection;
                sqlStatement.text = query;
            }
            else
            {
                _myLogger.debug("Using stored statement");
            }
			_myLogger.debug("sqlStatement.text = " + sqlStatement.text);
            processParameters(sqlStatement, parameters, handler, queryLimit);      
        }
        
        /**
         * Apply the parameters to the SQL Statement and start the execution of the statement.
         * @param sqlStatement SQL Statement to apply parameters to.
         * @param parameters Parameters to apply to query.
         * @param handler Function to get SQLResult object. 
         * @param queryLimit The maximum number of rows to return for the query.
         */ 
        private function processParameters(sqlStatement:SQLStatement, parameters:Object, handler:Function, queryLimit:int):void
        {
            _myLogger.debug("process parameters");
            sqlStatement.clearParameters();
            if (parameters != null)
            {
                for (var name:String in parameters)
                {
                    // To make parameter object creation easier the standard
                    // ":" prefix is added here when setting the value. 
                    _myLogger.debug("parameters[" + name + "] = " + parameters[name]);
                    sqlStatement.parameters[":" + name] = parameters[name];
                }
            }
            sqlStatement.addEventListener(SQLEvent.RESULT, function(event:Event):void
            {
                sqlStatement.removeEventListener(SQLEvent.RESULT, arguments.callee);
                processResults(sqlStatement, handler);
            });
            _myLogger.debug("calling execute");
            sqlStatement.execute(queryLimit);
        } 
        
        /**
         * Handle the results of the execution. This method passes the SQL Result
         * object to the handler and returns the connection to the connection pool. 
         * @param sqlStatement SQL Statement to apply parameters to.
         * @param handler Function to get SQLResult object.
         */ 
        private function processResults(sqlStatement:SQLStatement, handler:Function):void
        {
            _myLogger.debug("process results");
            var sqlResult:SQLResult = sqlStatement.getResult();
            _myLogger.debug("sqlResult.data = " + sqlResult.data);
            // If there are too many results returned StatementPool delegates the
            // processing to the handler and makes no attempt to divide up the
            // records returned using next().
            if (!sqlResult.complete)
            {
                sqlStatement.cancel();
            }
            _connectionPool.returnConnection(sqlStatement.sqlConnection);
            handler(sqlResult);
        }
    }
}
